package com.csii.excelapp.controller.excel;

import cn.hutool.core.util.StrUtil;
import com.csii.excelapp.entity.RequestDto;
import com.csii.excelapp.entity.ResponseDto;
import com.csii.excelapp.service.IExcelService;
import com.csii.excelapp.util.ExcelUtil;
import com.csii.excelapp.util.IndexConstant;
import com.google.api.client.util.ArrayMap;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;

import java.io.File;
import java.nio.file.Paths;
import java.util.*;

@Controller
@RequestMapping("/excel")
public class ExcelController {

    private static final Logger log = LoggerFactory.getLogger(ExcelController.class);

    @Autowired
    private IExcelService excelService;

    @RequestMapping("/ping")
    @ResponseBody
    public String test(){
        return "pong";
    }

    @RequestMapping("/build")
    @ResponseBody
    public Object build(@RequestBody RequestDto request) {

        //fixme 参数校验未做
        String tempFilePath = request.getTempFilePath();
        String finalExcelFilePath = request.getFinalExcelFilePath();
        String version = request.getVersion();
        String pageType = request.getPageType();
        String dataSourceType = request.getDataSourceType();
        Map frontParam = request.getFrontParams();

        ResponseDto<Object> resp = new ResponseDto<>();
        String reportBatch = "";

        //1.1,确定报表模板路径
        //1.2 从模板文件中获取所有参变量定义,一个 excelUtil 对应操作一个 excel 模板文件，可同时操作多个
        ExcelUtil excelUtil = null;
        Map<String, String> definedVars = null;
        try {
            excelUtil = new ExcelUtil(new File(tempFilePath).toString());
            definedVars = excelUtil.getDefinedVars(excelUtil.getWorkbook());
        } catch (Exception e) {
            resp.setSuccess(false);
            resp.setMessage("生成报表时发生错误，详情见日志");
            e.printStackTrace();
        }

        String fileName = new File(tempFilePath).getName();
        if (!StrUtil.hasEmpty(version)) {
            fileName = fileName.substring(0, fileName.lastIndexOf(".")) + "_" + version + fileName.substring(fileName.lastIndexOf("."), fileName.length());
        }
        String excelFileSavedPath = new File("").toString();//TemplateUtil.genTemplateUrl();
        String excelFile = Paths.get(excelFileSavedPath, fileName).toString();

        //1.3,遍历map进行数据库取数，生成一个Map,如果数据有误，则返回一标记一个"sql error"
        if (!StrUtil.hasEmpty(pageType) && "show".equals(pageType)) {
            Map<String, Object> paraMap = new LinkedHashMap<>();
            Set<Map.Entry<String, String>> entries = definedVars.entrySet();
            for (Map.Entry<String, String> entry : entries) {
                String key = entry.getKey();
                String value = entry.getValue();
                value = value.replace("#D", reportBatch);
                String tmpStr[] = key.split("@");
                String varName = tmpStr[1];
                if (tmpStr.length < 2) {
                    paraMap.put(key, "参数不正确[" + key + "]");
                } else {
                    if (varName.startsWith("$") && !varName.startsWith("$S")) {
                        List<Map<String, Object>> result = null;
                    /* 用前端传过来参变量的值去替换 sql 中的$参变量,未作严谨判断，即，使用正则表达式判断 sql 中的取一个参变量，如果没有完全匹配，则报错
                       现仅当前端有传过来对应的参变量名时，才去替换 sql 语句中对应的值，如果没有，或者多，或者少，均不判断，如果执行出错，待后期增加严谨
                       判断和异常处理
                     */
                        if (value.contains("$")) {
                            // Map frontParam = frontParams.get("frontParam");
                            Set set = frontParam.keySet();
                            for (Object tempKey : set) {
                                String sqlVarName = (String) tempKey;
                                String sqlStr = (String) frontParam.get(sqlVarName);
                                value = value.replace(sqlVarName, sqlStr);
                            }
                        }
                        try {
                            // 理论上就只能有一个值
                            //将所有 value 字符串中因各种原因出现的特殊不可见字符替换为空格，否则 sql 执行不会成功
                            String sql = value.replaceAll("\\p{C}", " ")
                                    .replaceAll("[;\\t\\n\\r]+", "");
                            sql = sql.trim();
                            //只允许执行 select 语句
                            boolean beSelect = sql.trim().matches("^(?i)select +.+");
                            if (beSelect) {
                                result = excelService.getSqlResult(sql);
                            } else {
                                result = new ArrayList<>();
                            }
                            if (result == null || !(result instanceof List) || result.size() != 1 || null == result.get(0) || !(result.get(0) instanceof Map)) {
                                throw new Exception();
                            }
                            Map<String, Object> stringObjectMap = result.get(0);
                            for (String string : stringObjectMap.keySet()) {
                                paraMap.put(key, stringObjectMap.get(string));
                            }
                        } catch (Exception e) {
                            //如果未查到值，则填入一个 sql error 提醒，而不是中止程序
                            paraMap.put(key, varName + " sql error");
                        }
                    } else if (varName.startsWith("$S")) {
                        //1.4,以value为条件查询库
                        List<Map<String, Object>> result = null;
                        //如果未查到值，则填入一个空，而不是中止程序
                        if (value.contains("$")) {
//                            LinkedHashMap frontParam = (LinkedHashMap) frontParams.get("frontParam");
                            Set set = frontParam == null ? new HashSet<>() : frontParam.keySet();
                            for (Object tempKey : set) {
                                String sqlVarName = (String) tempKey;
                                String sqlStr = (String) frontParam.get(sqlVarName);
                                value = value.replace(sqlVarName, sqlStr);
                            }
                        }
                        //删除 sql 语句的末尾 ; 号
                        if (value != null || value.endsWith(";")) {
                            value = value.replace(";", "");
                        }
                        //将所有 value 字符串中因各种原因出现的特殊不可见字符替换为空格，否则 sql 执行不会成功
                        String sql = value.replaceAll("\\p{C}", " ")
                                .replaceAll("[;\\t\\n\\r]+", "");
                        try {
                            sql = sql.trim();
                            //只允许执行 select 语句
                            boolean beSelect = sql.trim().matches("^(?i)select +.+");
                            if (beSelect) {
                                result = excelService.getSqlResult(sql);
                            } else {
                                result = new ArrayList<>();
                            }
                        } catch (Exception e) {
                            result = new ArrayList<>();
                            LinkedHashMap<String, Object> tempMap = new LinkedHashMap<>();
                            tempMap.put(varName, varName + " sql error");
                            result.add(tempMap);
                            paraMap.put(key, result);
                        }

                        String trim = sql.substring(sql.toUpperCase().indexOf("SELECT") + 6, sql.toUpperCase().indexOf("FROM"));
                        List<String> listKey = Arrays.asList(trim.split(","));

                        Map<String, List<Object>> sqlMap = new LinkedHashMap<>();
                        for (Map<String, Object> map : result) {
                            if (map != null && result.get(0) == null) {
                                break;
                            }
                            if (map != null) {
                                Set<Map.Entry<String, Object>> entries1 = map.entrySet();
                                for (Map.Entry<String, Object> entry1 : entries1) {
                                    String col = entry1.getKey();
                                    Object field = entry1.getValue() == null ? "" : entry1.getValue();
                                    //获取原有 paramMap 中的既有项 Map<String,Map<String,list<String>>>
                                    List<Object> sqlList = sqlMap.get(col);
                                    if (sqlList == null) {
                                        sqlList = new ArrayList<>();
                                    }
                                    sqlList.add(field);
                                    sqlMap.put(col, sqlList);
                                }
                            } else {
                                for (String colKey : listKey) {
                                    List<Object> sqlList = sqlMap.get(colKey.trim());
                                    if (sqlList == null) {
                                        sqlList = new ArrayList<>();
                                    }
                                    sqlList.add("");
                                    sqlMap.put(colKey.trim(), sqlList);
                                }
                            }
                        }
                        paraMap.put(key, sqlMap);
                    }
                    //将所有固定指标，填入待填充值列表中
                    Map<String, String> constantMap = IndexConstant.getConstantMap();
                    Set<Map.Entry<String, String>> entries2 = constantMap.entrySet();
                    for (Map.Entry<String, String> map : entries2) {
                        String constkey = map.getKey();
                        String constValue = map.getValue();
                        String combKey = "_@" + constkey;
                        paraMap.put(combKey, constValue);
                    }
                }
            }

            //把前端传过来的 固定全局替换值 填充到参量表中
//            LinkedHashMap frontParam = (LinkedHashMap) frontParams.get("frontParam") == null ? new LinkedHashMap() : (LinkedHashMap) frontParams.get("frontParam");
            Set set = (frontParam == null ? set = new HashSet() : frontParam.entrySet());
            for (Object obj : set) {
                Map.Entry<String, String> map = (Map.Entry<String, String>) obj;
                String varName = map.getKey();
                String value = map.getValue();
                if (varName.startsWith("$")) {
                    paraMap.put("_@" + varName, value);
                }
            }
            //1.6把paraMap传给 excelUtil 对象生成具体报表`
            String excelAbsFile = excelFile;
            Map<String, String> addinMap = new ArrayMap<>();
            addinMap.put("flag", (String) frontParam.get("status"));
            addinMap.put("reset", (String) frontParam.get("type"));
            //paraMap.put("0@$yyyy$mm$dd", "20330303");
            finalExcelFilePath.replaceAll("\\.xlsx", "");
            excelAbsFile = finalExcelFilePath + UUID.randomUUID().toString().replaceAll("-", "").substring(3, 8) + ".xlsx";
            try {
                excelUtil.fillVars2ExcelInstance(excelUtil.getWorkbook(), excelAbsFile, paraMap, addinMap);
            } catch (Exception e) {
                resp.setSuccess(false);
                resp.setMessage("生成 excel 报表时发生异常");
                e.printStackTrace();
            }
        }
        return resp;
    }
}
